package org.study.clickhouse.boot.repository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.study.clickhouse.boot.model.User;
import org.study.clickhouse.boot.util.SqlDateUtils;

import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @author LENOVO
 */
@Repository
public class UserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final String c = "id, username, password, phone, email, birthday, created_at, updated_at";

    @Transactional(readOnly = true)
    public Long selectCount() {
        String sql = "select count(*) from t_user";
        Long count = jdbcTemplate.queryForObject(sql, Long.class);
        return count;
    }

    @Transactional(readOnly = true)
    public List<Map<String, Object>> selectAll4Map() {
        String sql = "select id, username, password, phone, email, birthday, created_at, updated_at from t_user";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Transactional(readOnly = true)
    public List<User> selectAll4List() {
        String sql = "select id, username, password, phone, email, birthday, created_at, updated_at from t_user";
        return jdbcTemplate.query(sql, new UserRowMapper());
    }

    @Transactional(readOnly = true)
    public User selectById(long id) {
        String sql = "select id, username, password, phone, email, birthday, created_at, updated_at from t_user where id = ? limit 1";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new UserRowMapper());
    }

    @Transactional(readOnly = true)
    public User selectById2(long id) {
        String sql = "select id, username, password, phone, email, birthday, created_at, updated_at from t_user where id = ? limit 1";
        List<User> list = jdbcTemplate.query(sql, new Object[]{id}, new UserRowMapper());
        if (CollectionUtils.isEmpty(list)) {
            return null;
        }
        return list.get(0);
    }

    public int insert(final User user) {
        final String sql = "insert into t_user(id, username, password, phone, email, birthday, created_at, updated_at) values(?, ?, ?, ?, ?, ?, ?, ?)";
        int update = jdbcTemplate.update(sql, user.getId(), user.getUsername(), user.getPassword(), user.getPhone(), user.getEmail(), user.getBirthday(), user.getCreatedAt(), user.getUpdatedAt());
        return update;
    }

    public int delete(final long id) {
        final String sql = "alter table t_user delete where id = ?";
        return jdbcTemplate.update(sql, new Object[]{id}, new int[]{Types.BIGINT});
    }

    public int updateById(final User user) {
        String sql = "alter table t_user update username = ?, updated_at = now() where id = ?";
        int update = jdbcTemplate.update(sql, new Object[]{user.getUsername(), user.getId()});
        return update;
    }

    class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            // id, username, password, phone, email, birthday, created_at, updated_at
            user.setId(resultSet.getLong("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setPhone(resultSet.getString("phone"));
            user.setEmail(resultSet.getString("email"));
            user.setBirthday(SqlDateUtils.sqlDate2LocalDate(resultSet.getDate("birthday")));
            user.setCreatedAt(SqlDateUtils.sqlTimestamp2LocalDateTime(resultSet.getTimestamp("created_at")));
            user.setUpdatedAt(SqlDateUtils.sqlDate2UtilDate(resultSet.getDate("updated_at")));
            return user;
        }
    }
}